#!/usr/bin/env python
# -*- coding: utf-8 -*-

import csv
import cx_Oracle as cx


class BoardUtil:

    def __init__(self):
        """
        初始化数据库连接和session
        """
        # 连接数据库，创建session
        super().__init__()
        self.connection = cx.connect('scott', 'tiger', '127.0.0.1:1521/adam')
        self.cursor = self.connection.cursor()

    def update_stock_amount(self):
        """
        更新board表的stock_amount字段
        :return:
        """
        sql_group_by_board_id = 'select t.board_id, count(*) from STOCK_INFO t where t.board_id is not null group by t.board_id'
        self.cursor.execute(sql_group_by_board_id)
        for board_id, count in self.cursor.fetchall():
            sql_update_stock_amount = 'update board set stock_amount=' + str(count) + ' where id=' + str(board_id)
            self.cursor.execute(sql_update_stock_amount)
            self.connection.commit()


if __name__ == '__main__':
    board_util = BoardUtil()
    board_util.update_stock_amount()
